package com.aaa.sbms.dao;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface GroupDao {

    /**
     * 查询部门
     * @return
     */
    @Select("select * from depts where deptid!=5")
    List<Map> depts();

    /**
     * 查询班组领导人
     * @return
     */
    @Select("select * from users")// u where u.userid not in (select gleaderid from groups) and u.leaderid is null
    List<Map> users();

    /**
     * 分页查询班组
     * @return
     */
    @Select("<script>select g.*,d.dname,u.username from groups g left join depts d on d.deptid=g.deptid\n" +
            "left join users u on u.userid=g.gleaderid\n" +
            "<where>d.deptid!=5\n" +
            "<if test=\"groupid!=null and groupid!=''\">and g.groupid=#{groupid}</if>" +
            "<if test='gname!=null'>and g.gname like '%${gname}%'</if>" +
            "<if test='dname!=null'>and d.dname like '%${dname}%'</if>" +
            "</where>order by g.groupid desc limit #{begin},#{pageSize}</script>")
    List<Map> page(Map map);

    /**
     * 分页查询班组总数量
     * @return
     */
    @Select("<script>select count(*) from groups g left join depts d on d.deptid=g.deptid\n" +
            "left join users u on u.userid=g.gleaderid\n" +
            "<where>d.deptid!=5\n" +
            "<if test=\"groupid!=null and groupid!=''\">and g.groupid=#{groupid}</if>" +
            "<if test='gname!=null'>and g.gname like '%${gname}%'</if>" +
            "<if test='dname!=null'>and d.dname like '%${dname}%'</if>" +
            "</where></script>")
    int pageCount(Map map);

    /**
     * 查询一个班组
     * @param groupid
     * @return
     */
    @Select("select * from groups g left join depts d on d.deptid=g.deptid\n" +
            "left join users u on u.userid=g.gleaderid\n" +
            "where g.groupid=#{groupid}")
    Map groupOne(int groupid);

    /**
     * 添加
     * @param map
     * @return
     */
    @Insert("insert into groups values(null,#{gname},#{deptid},#{gleaderid},#{remark})")
    int add(Map map);

    /**
     * 修改
     * @param map
     * @return
     */
    @Update("update groups set gname=#{gname},deptid=#{deptid},gleaderid=#{gleaderid},remark=#{remark}\n" +
            "where groupid=#{groupid}")
    int update(Map map);

    /**
     * 删除
     * @param groupid
     * @return
     */
    @Delete("delete from groups where groupid=#{groupid}")
    int delete(int groupid);
}
